﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Odbc;

namespace CocaCola_Inventory_System.Data_Access
{
    class searchUsers
    {
        private static OdbcDataAdapter dAdapt;
        private static OdbcConnection connection;

        /// <summary>
        /// Searches the users' database
        /// </summary>
        /// <param name="con">Existing connection</param>
        /// <param name="searchString">string to be searched</param>
        /// <param name="field">field to be searched</param>
        /// <param name="match">should the search exactly match</param>
        /// <param name="group">a string containing columns to be grouped; e.g.: title AND department</param>
        /// <returns>DataTable containing the results</returns>
        public static DataTable search(OdbcConnection con,string searchString, int field,bool match,string group)
        {

            try
            {
                if (match)
                    searchString = "'" + searchString + "'";
                else
                    searchString = "'%" + searchString + "%'";

                string column = "";

                #region selecting column
                switch (field)
                {
                    case 0:
                        if (match)
                            column = string.Format("Name = {0} OR title = {0} OR department = {0} OR department_no = {0} OR telephone = {0} OR mobile = {0} OR extension_attribute2 = {0} OR home_directory = {0} OR home_drive = {0} OR postal_address = {0} OR postal_code = {0} OR description = {0} OR city = {0} OR country = {0} OR email = {0} OR loginid = {0}", searchString);
                        else
                            column = string.Format("lower(Name) LIKE lower({0}) OR lower(title) LIKE lower({0}) OR lower(department) LIKE lower({0}) OR lower(department_no) LIKE lower({0}) OR lower(telephone) LIKE lower({0}) OR lower(mobile) LIKE lower({0}) OR lower(extension_attribute2) LIKE lower({0}) OR lower(home_directory) LIKE lower({0}) OR lower(home_drive) LIKE lower({0}) OR lower(postal_address) LIKE lower({0}) OR lower(postal_code) LIKE lower({0}) OR lower(description) LIKE lower({0}) OR lower(city) LIKE {0} OR lower(country) LIKE lower({0}) OR lower(email) LIKE lower({0}) OR lower(loginid) LIKE lower({0})", searchString);
                        break;
                    case 1:
                        column = "Name";
                        break;
                    case 2:
                        column = "title";
                        break;
                    case 3:
                        column = "department";
                        break;
                    case 4:
                        column = "department_no";
                        break;
                    case 5:
                        column = "email";
                        break;
                    case 6:
                        column = "telephone";
                        break;
                    case 7:
                        column = "mobile";
                        break;
                    case 8:
                        column = "postal_address";
                        break;
                    case 9:
                        column = "postal_code";
                        break;
                    case 10:
                        column = "home_directory";
                        break;
                    case 11:
                        column = "home_drive";
                        break;
                    case 12:
                        column = "description";
                        break;
                    case 13:
                        column = "loginid";
                        break;
                    case 14:
                        column = "city";
                        break;
                    case 15:
                        column = "country";
                        break;
                    case 16:
                        column = "extension_attribute2";
                        break;
                }
                #endregion

                if (con == null)
                    throw new Exception("Null Connetion");

                connection = con;
                OdbcCommand com = new OdbcCommand();
                com.Connection = con;

                if (field == 0)
                {
                    if (group.Length > 0)
                        com.CommandText = string.Format("SELECT * FROM users WHERE {0} GROUP BY {1};", column, group);
                    else
                        com.CommandText = string.Format("SELECT * FROM users WHERE {0};", column);
                }
                else
                {
                    if (match)
                    {
                        if (group.Length > 0)
                            com.CommandText = string.Format("SELECT * FROM users WHERE {0}={1} GROUP BY {2};", column, searchString, group);
                        else
                            com.CommandText = string.Format("SELECT * FROM users WHERE {0}={1};", column, searchString);
                    }
                    else
                    {
                        if (group.Length > 0)
                            com.CommandText = string.Format("SELECT * FROM users WHERE lower({0}) LIKE lower({1}) GROUP BY {2};", column, searchString, group);
                        else
                            com.CommandText = string.Format("SELECT * FROM users WHERE lower({0}) LIKE lower({1});", column, searchString);
                    }
                }

                if (con.State != ConnectionState.Open)
                    con.Open();

                DataTable result = new DataTable();

                dAdapt = new OdbcDataAdapter(com);
                dAdapt.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                dAdapt.Fill(result);

                #region prepare adapter to update the db
                dAdapt.InsertCommand = new OdbcCommand("INSERT INTO users  (Name,title,department,department_no,telephone,mobile,extension_attribute2,home_directory,home_drive,postal_address,postal_code,description,city,country,email,loginid) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);",con);
                dAdapt.UpdateCommand = new OdbcCommand("UPDATE users SET id = ?, Name = ?,title = ?,department = ?,department_no = ?,telephone = ?,mobile = ?,extension_attribute2 = ?,home_directory = ?,home_drive = ?,postal_address = ?,postal_code = ?,description = ?,city = ?,country = ?,email = ?,loginid = ? WHERE id = ?;", con);
                dAdapt.DeleteCommand = new OdbcCommand("DELETE FROM users WHERE id=?;",con);

                dAdapt.InsertCommand.Parameters.Add("@id", OdbcType.Int, sizeof(int), "id");
                dAdapt.InsertCommand.Parameters.Add("@Name", OdbcType.VarChar, 200, "Name");
                dAdapt.InsertCommand.Parameters.Add("@title", OdbcType.VarChar, 45, "title");
                dAdapt.InsertCommand.Parameters.Add("@department", OdbcType.VarChar, 45, "department");
                dAdapt.InsertCommand.Parameters.Add("@department_no", OdbcType.VarChar, 45, "department_no");
                dAdapt.InsertCommand.Parameters.Add("@telephone", OdbcType.VarChar, 45, "telephone");
                dAdapt.InsertCommand.Parameters.Add("@mobile", OdbcType.VarChar, 45, "mobile");
                dAdapt.InsertCommand.Parameters.Add("@extension_attribute2", OdbcType.VarChar, 45, "extension_attribute2");
                dAdapt.InsertCommand.Parameters.Add("@home_directory", OdbcType.VarChar, 45, "home_directory");
                dAdapt.InsertCommand.Parameters.Add("@home_drive", OdbcType.VarChar, 45, "home_drive");
                dAdapt.InsertCommand.Parameters.Add("@postal_address", OdbcType.VarChar, 45, "postal_address");
                dAdapt.InsertCommand.Parameters.Add("@postal_code", OdbcType.VarChar, 45, "postal_code");
                dAdapt.InsertCommand.Parameters.Add("@description", OdbcType.VarChar, 45, "description");
                dAdapt.InsertCommand.Parameters.Add("@city", OdbcType.VarChar, 45, "city");
                dAdapt.InsertCommand.Parameters.Add("@country", OdbcType.VarChar, 45, "country");
                dAdapt.InsertCommand.Parameters.Add("@email", OdbcType.VarChar, 150, "email");
                dAdapt.InsertCommand.Parameters.Add("@loginid", OdbcType.VarChar, 45, "loginid");

                dAdapt.UpdateCommand.Parameters.Add("@id", OdbcType.Int, sizeof(int), "id");
                dAdapt.UpdateCommand.Parameters.Add("@Name", OdbcType.VarChar, 200, "Name");
                dAdapt.UpdateCommand.Parameters.Add("@title", OdbcType.VarChar, 45, "title");
                dAdapt.UpdateCommand.Parameters.Add("@department", OdbcType.VarChar, 45, "department");
                dAdapt.UpdateCommand.Parameters.Add("@department_no", OdbcType.VarChar, 45, "department_no");
                dAdapt.UpdateCommand.Parameters.Add("@telephone", OdbcType.VarChar, 45, "telephone");
                dAdapt.UpdateCommand.Parameters.Add("@mobile", OdbcType.VarChar, 45, "mobile");
                dAdapt.UpdateCommand.Parameters.Add("@extension_attribute2", OdbcType.VarChar, 45, "extension_attribute2");
                dAdapt.UpdateCommand.Parameters.Add("@home_directory", OdbcType.VarChar, 45, "home_directory");
                dAdapt.UpdateCommand.Parameters.Add("@home_drive", OdbcType.VarChar, 45, "home_drive");
                dAdapt.UpdateCommand.Parameters.Add("@postal_address", OdbcType.VarChar, 45, "postal_address");
                dAdapt.UpdateCommand.Parameters.Add("@postal_code", OdbcType.VarChar, 45, "postal_code");
                dAdapt.UpdateCommand.Parameters.Add("@description", OdbcType.VarChar, 45, "description");
                dAdapt.UpdateCommand.Parameters.Add("@city", OdbcType.VarChar, 45, "city");
                dAdapt.UpdateCommand.Parameters.Add("@country", OdbcType.VarChar, 45, "country");
                dAdapt.UpdateCommand.Parameters.Add("@email", OdbcType.VarChar, 150, "email");
                dAdapt.UpdateCommand.Parameters.Add("@loginid", OdbcType.VarChar, 45, "loginid");
                dAdapt.UpdateCommand.Parameters.Add("@oldid", OdbcType.Int, sizeof(int), "id").SourceVersion = DataRowVersion.Original;

                dAdapt.DeleteCommand.Parameters.Add("@id", OdbcType.Int, sizeof(int), "id").SourceVersion = DataRowVersion.Original;

                #endregion
                return result;
            }
            catch (Exception ex)
            {
                Common.ReportError(ex);
                return null;
            }
        }

        /// <summary>
        /// Updates the previously retrieved datatable
        /// </summary>
        /// <param name="input">DataTable containing the changes done</param>
        /// <returns>True if successfull</returns>
        public static bool update(DataTable input)
        {
            try
            {
                if (connection == null)
                    throw new Exception("Null Connetion");

                if (connection.State != ConnectionState.Open)
                    connection.Open();



                if (dAdapt != null)
                {
                    dAdapt.Update(input);
                }
                else
                    throw new Exception("Null Data Adapter");

                return true;
            }
            catch (Exception ex)
            {
                Common.ReportError(ex);
                //System.Diagnostics.Trace.WriteLine(dAdapt.UpdateCommand.Parameters.ToString());
                return false;
            }
        }
    }
}
